﻿-- Function: pl_function.panel_persona(character varying, refcursor)

-- DROP FUNCTION pl_function.panel_persona(character varying, refcursor);

CREATE OR REPLACE FUNCTION pl_function.panel_persona(p_mperson character varying, p_ref refcursor)
  RETURNS refcursor AS
$BODY$
begin
	open p_ref for select 
		  a.mhresum, a.cidpers, a.crazsoc, a.direccf
		, a.vtipdoc, a.nnrodoc
		, to_char(a.nbaseim, 'FM9999990.00') nbaseim
		, to_char(a.ntotpre, 'FM9999990') ntotpre
		, to_char(a.nimpanu, 'FM9999990.00') nimpanu
		, a.vnrodoc
		, to_char(a.dfecdoc, 'dd/MM/yyyy') dfecdoc
		, a.vmotivo
		, 0 ntotcom
		, 'No'::character(2) vprofis
		, 'No'::character(2) vprotra
		, 'No'::character(2) vprofis
		, coalesce(to_char(b.dfecpag, 'dd/MM/yyyy'), '---') dfecpag -- hh:mi:ss
		, to_char(b.nmontot, 'FM9999990.00') nmontot
		, b.cnumcom
	from registro.vwhresum a
		left join(
			select cidpers, cnumcom, nmontot, dfecpag from tesoreria.mtesore x where 
				x.cidpers = p_mperson and 
				x.nestado = '1' and 
				dfecpag in (
					select max(dfecpag) from tesoreria.mtesore z where z.cidpers=x.cidpers and z.nestado='1'
				)
			order by cnumcom limit 1
		) b on a.cidpers=b.cidpers
	where a.cidpers = p_mperson
		and a.cperiod = (select max(b.cperiod) from registro.vwhresum b where b.cidpers=p_mperson)
		and a.nestado = '1' and a.ctippro='1000000451'; -- and a.destado = 1;
		
	return p_ref;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.panel_persona(character varying, refcursor)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.panel_persona(character varying, refcursor) TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.panel_persona(character varying, refcursor) TO public;
GRANT EXECUTE ON FUNCTION pl_function.panel_persona(character varying, refcursor) TO desarrollo;
